﻿<%@ WebHandler Language="C#" Class="CheckElementsCountDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 检验要素数量统计看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.8.26
 */

public class CheckElementsCountDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getToday":
                        r = getData(jObject);//今日
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonth":
                        r = getData(jObject);//本月
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisYear":
                        r = getData(jObject);//本年
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询公用sql
    string getQuerySql(JObject jObject)
    {
        string sql = @"
SELECT m.employeename,m.fullname,SUM(m.qty * m.checkQty) qty 
FROM (
   SELECT e.employeeid,e.employeename,e.fullname,c.containername,c.qty,ws.workflowstepname,cfc.containerfinishedcheckinfoid,COUNT(ccd.employeeid) checkQty
   FROM containerfinishedcheckinfo cfc
   LEFT JOIN container c ON cfc.containerid = c.containerid
   LEFT JOIN containerfinishedcheckdetail ccd ON cfc.containerfinishedcheckinfoid = ccd.containerfinishedcheckinfoid
   LEFT JOIN factory f ON f.factoryid = cfc.factoryid
   LEFT JOIN employee e ON e.employeeid = ccd.employeeid
   LEFT JOIN workflowstep ws ON cfc.workflowstepid = ws.workflowstepid
   WHERE e.employeeid IS NOT NULL AND ccd.checkdetailname LIKE '%零件尺寸%'
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format("AND f.factoryid = '{0}' ", jObject["factoryid"].ToString());
        }

        if (jObject["searchType"].ToString() == "today")
        {
            JArray arr = JArray.Parse(jObject["thisDay"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(ccd.createdate) = to_date('{0}','YYYY/MM/DD') ", arr[0].ToString());
            }
        }
        else if (jObject["searchType"].ToString() == "thisMonth")
        {
            JArray arr = JArray.Parse(jObject["thisMonth"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(ccd.createdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
            }
        }
        else
        {
            JArray arr = JArray.Parse(jObject["thisYear"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(ccd.createdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
            }
        }
        sql += @"
GROUP BY e.employeeid,e.employeename,e.fullname,c.containername,c.qty,ws.workflowstepname,cfc.containerfinishedcheckinfoid
) M
GROUP BY m.employeename,m.fullname";

        return sql;
    }

    //查询待检验信息 add zhunan 2022.8.26
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = getQuerySql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dtResult;
        r.total = Convert.ToInt32(dtResult.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}